﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Web;
using System.Xml;

using Beeldbankmvc.Code;
using Beeldbankmvc.Controllers;

namespace Beeldbankmvc.Models
{
    public class ReportRepository
    {
        BeeldbankLinqDataContext db = new BeeldbankLinqDataContext();

        MediaObjectRepository mediaObjectRepository = new MediaObjectRepository();

        public IEnumerable<ReactionStatusCountModel> getStatusCounts(DateTime fromDate, DateTime toDate)
        {
            var statusCounts = from c in db.BEELDBANK_REACTIEs
                               where c.Datum > fromDate && c.Datum < toDate
                               group c by c.Status into g
                               orderby g.Count() descending
                               select new ReactionStatusCountModel { StatusId = g.Key, StatusCount = g.Count() };
            return statusCounts;
        }

        public IEnumerable<ReactionTypeCountModel> getTypeCounts(DateTime fromDate, DateTime toDate)
        {
            var typeCounts = from c in db.BEELDBANK_REACTIEs
                               where c.Datum > fromDate && c.Datum < toDate
                               group c by c.Subject into g
                               orderby g.Count() descending
                               select new ReactionTypeCountModel { TypeId = g.Key, TypeCount = g.Count() };
            return typeCounts;
        }
        
        public IEnumerable<ReactionCategoryCountModel> getCategoryCounts(DateTime fromDate, DateTime toDate)
        {
            var categoryCounts = from c in db.BEELDBANK_REACTIEs
                             where c.Datum > fromDate && c.Datum < toDate
                             group c by c.Category into g
                             orderby g.Count() descending
                             select new ReactionCategoryCountModel { CategoryId = g.Key, CategoryCount = g.Count() };
            return categoryCounts;
        }
        
        public IEnumerable<MostWantedModel> getMostWantedList(DateTime fromDate, DateTime toDate)
        {
            var searchCounts = from c in db.BEELDBANK_ZOEKACTIELOGs
                               where c.ZoekDatum > fromDate && c.ZoekDatum < toDate
                               group c by c.Criteria into g
                               orderby g.Count() descending
                               select new MostWantedModel { Criteria = g.Key, SearchCount = g.Count() };

            searchCounts = searchCounts.Take(11);

            return searchCounts;
        }
        
        public IEnumerable<RecentlyAddedMediaModel> getRecentlyAddedMediaItemsList(DateTime fromDate, DateTime toDate, int Count = 3)
        {
            var searchCounts = from m in db.AddedMediaObjectsPerWeekNr(fromDate, toDate)
                               select new RecentlyAddedMediaModel
                               {
                                   Year = (m.Year.HasValue) ? m.Year.Value.ToString() : "-",
                                   Week = (m.Week.HasValue) ? m.Week.Value.ToString() : "-",
                                   Count = (m.MediaObjectCount.HasValue) ? m.MediaObjectCount.Value : 0
                               };

            return searchCounts.Take(Count);
        }
        
        public IQueryable<CollectionNumbersModel> getCollectionNumbersList(DateTime fromDate, DateTime toDate)
        {
            var collectionNumbers = from m in db.BEELDBANK_MEDIAOBJECTs
                                    where m.createdat > fromDate && m.createdat < toDate && m.StatusId == 11911
                                    group m by m.TypeId into g
                                    orderby g.Count() descending
                                    select new CollectionNumbersModel
                                    {
                                        MediaType = Mediatypes.mediaTypeDescription((Mediatypes.MediaTypes)g.Key),
                                        ItemCount = g.Count()
                                    };

            return collectionNumbers;
        }
        
        public NumberOfSearchesModel getNumberOfSearches(DateTime fromDate, DateTime toDate)
        {
            var searchCounts = from c in db.BEELDBANK_ZOEKACTIELOGs
                               where c.ZoekDatum > fromDate && c.ZoekDatum < toDate
                               select c;

            NumberOfSearchesModel model = new NumberOfSearchesModel();
            model.NumberOfSearches = searchCounts.Count();

            return model;
        }

        public IEnumerable<ViewCounterModel> getViewCounters(DateTime fromDate, DateTime toDate, bool masteredMovies, int pageSize, int pageIndex, string publicationName)
        {
            string montage = "Ongemonteerd";
            if (masteredMovies)
                montage = "Gemonteerd";
            
            var searchCounts = from vc in db.BEELDBANK_VIEW_COUNTERs
                               join m in db.BEELDBANK_MEDIAOBJECTs on vc.MediaObjectId equals m.Id
                               where
                               vc.BEELDBANK_MEDIAOBJECT.Montage.StartsWith(montage) &&
                               vc.Publicatie == publicationName &&
                               vc.LastUpdate > fromDate &&
                               vc.LastUpdate < toDate
                               orderby vc.ViewCount descending
                               select new ViewCounterModel { Omschrijving = AppHelper.convertHtmlStringToAltTag(m), MediaObjectId = vc.MediaObjectId, CreatedDate = vc.CreationDate, LastClicked = vc.LastUpdate, Clicks = vc.ViewCount };

            return searchCounts.Skip(pageIndex * pageSize).Take(pageSize);
        }

        public IEnumerable<ViewCounterModel> getViewCountersFoto(DateTime fromDate, DateTime toDate, int pageSize, int pageIndex, string publicationName)
        {
            var searchCounts = from vc in db.BEELDBANK_VIEW_COUNTERs
                               join m in db.BEELDBANK_MEDIAOBJECTs on vc.MediaObjectId equals m.Id
                               where vc.BEELDBANK_MEDIAOBJECT.TypeId == Convert.ToInt32(Mediatypes.MediaTypes.digital) && 
                               vc.Publicatie == publicationName &&
                               vc.LastUpdate > fromDate &&
                               vc.LastUpdate < toDate
                               orderby vc.ViewCount descending
                               select new ViewCounterModel { Omschrijving = AppHelper.convertHtmlStringToAltTag(m), MediaObjectId = vc.MediaObjectId, CreatedDate = vc.CreationDate, LastClicked = vc.LastUpdate, Clicks = vc.ViewCount };

            return searchCounts.Skip(pageIndex * pageSize).Take(pageSize);
        }

        public int getNumberOfViewCounterRows(DateTime fromDate, DateTime toDate, bool masteredMovies, string publicationName)
        {
            string montage = "Ongemonteerd";
            if (masteredMovies)
                montage = "Gemonteerd";

            var searchCounts = from vc in db.BEELDBANK_VIEW_COUNTERs
                               where
                               vc.BEELDBANK_MEDIAOBJECT.Montage.StartsWith(montage) &&
                               vc.Publicatie == publicationName &&
                               vc.LastUpdate > fromDate &&
                               vc.LastUpdate < toDate
                               select vc;

            return searchCounts.Count();
        }

        public int getNumberOfViewCounterFotoRows(DateTime fromDate, DateTime toDate, string publicationName)
        {
            var searchCounts = from vc in db.BEELDBANK_VIEW_COUNTERs
                               where vc.BEELDBANK_MEDIAOBJECT.TypeId == Convert.ToInt32(Mediatypes.MediaTypes.digital) && 
                               vc.Publicatie == publicationName &&
                               vc.LastUpdate > fromDate &&
                               vc.LastUpdate < toDate
                               select vc;

            return searchCounts.Count();
        }

        public IEnumerable<ViewCounterModel> getViewCountersBestand(DateTime fromDate, DateTime toDate, int pageSize, int pageIndex, string publicationName)
        {
            var searchCounts = from vc in db.BEELDBANK_VIEW_COUNTERs
                               join m in db.BEELDBANK_MEDIAOBJECTs on vc.MediaObjectId equals m.Id
                               where vc.BEELDBANK_MEDIAOBJECT.TypeId == Convert.ToInt32(Mediatypes.MediaTypes.map_illustration) && 
                               vc.Publicatie == publicationName &&
                               vc.LastUpdate > fromDate &&
                               vc.LastUpdate < toDate
                               orderby vc.ViewCount descending
                               select new ViewCounterModel { Omschrijving = AppHelper.convertHtmlStringToAltTag(m), MediaObjectId = vc.MediaObjectId, CreatedDate = vc.CreationDate, LastClicked = vc.LastUpdate, Clicks = vc.ViewCount };

            return searchCounts.Skip(pageIndex * pageSize).Take(pageSize);
        }

        public int getNumberOfViewCounterBestandRows(DateTime fromDate, DateTime toDate, string publicationName)
        {
            var searchCounts = from vc in db.BEELDBANK_VIEW_COUNTERs
                               where vc.BEELDBANK_MEDIAOBJECT.TypeId == Convert.ToInt32(Mediatypes.MediaTypes.map_illustration) && 
                               vc.Publicatie == publicationName &&
                               vc.LastUpdate > fromDate &&
                               vc.LastUpdate < toDate
                               select vc;

            return searchCounts.Count();
        }
        
        public IEnumerable<ViewAnalogAlbumModel> getAnalogAlbumList(int pageSize, int pageIndex)
        {
            var AnalogAlbumList1 = (from vc in db.BEELDBANK_MEDIAOBJECTs
                                    where vc.Mapnummer.Length == 1
                                    select new ViewAnalogAlbumModel { Mapnummer = vc.Mapnummer }).Distinct();
            var AnalogAlbumList2 = (from vc in db.BEELDBANK_MEDIAOBJECTs
                                    where (vc.Mapnummer.Length == 2) && (vc.Mapnummer != "-1")
                                    select new ViewAnalogAlbumModel { Mapnummer = vc.Mapnummer }).Distinct();
            var AnalogAlbumList3 = (from vc in db.BEELDBANK_MEDIAOBJECTs
                                    where vc.Mapnummer.Length == 3
                                    select new ViewAnalogAlbumModel { Mapnummer = vc.Mapnummer }).Distinct();
            var AnalogAlbumList4 = (from vc in db.BEELDBANK_MEDIAOBJECTs
                                    where (vc.Mapnummer.Length == 4) && (vc.Mapnummer != "hoes")
                                    select new ViewAnalogAlbumModel { Mapnummer = vc.Mapnummer }).Distinct();
            var AnalogAlbumList5 = (from vc in db.BEELDBANK_MEDIAOBJECTs
                                    where (vc.Mapnummer.Length == 5) && (vc.Mapnummer != "hoes")
                                    select new ViewAnalogAlbumModel { Mapnummer = vc.Mapnummer }).Distinct();
            List<ViewAnalogAlbumModel> sortedList1 = new List<ViewAnalogAlbumModel>(AnalogAlbumList1);
            sortedList1.Sort(delegate(ViewAnalogAlbumModel x, ViewAnalogAlbumModel y) { return x.Mapnummer.CompareTo(y.Mapnummer); });
            List<ViewAnalogAlbumModel> sortedList2 = new List<ViewAnalogAlbumModel>(AnalogAlbumList2);
            sortedList2.Sort(delegate(ViewAnalogAlbumModel x, ViewAnalogAlbumModel y) { return x.Mapnummer.CompareTo(y.Mapnummer); });
            List<ViewAnalogAlbumModel> sortedList3 = new List<ViewAnalogAlbumModel>(AnalogAlbumList3);
            sortedList3.Sort(delegate(ViewAnalogAlbumModel x, ViewAnalogAlbumModel y) { return x.Mapnummer.CompareTo(y.Mapnummer); });
            List<ViewAnalogAlbumModel> sortedList4 = new List<ViewAnalogAlbumModel>(AnalogAlbumList4);
            sortedList4.Sort(delegate(ViewAnalogAlbumModel x, ViewAnalogAlbumModel y) { return x.Mapnummer.CompareTo(y.Mapnummer); });
            List<ViewAnalogAlbumModel> sortedList5 = new List<ViewAnalogAlbumModel>(AnalogAlbumList5);
            sortedList5.Sort(delegate(ViewAnalogAlbumModel x, ViewAnalogAlbumModel y) { return x.Mapnummer.CompareTo(y.Mapnummer); });

            return sortedList1.Concat(sortedList2.Concat(sortedList3.Concat(sortedList4.Concat(sortedList5)))).Skip(pageIndex * pageSize).Take(pageSize);
        }

        public int getNumberOfAnalogAlbumRows()
        {
            var searchCounts = (from vc in db.BEELDBANK_MEDIAOBJECTs
                                select vc.Mapnummer).Distinct();
            return searchCounts.Count();
        }
            
        public void GenerateVideoOverView()
        {
            var list = mediaObjectRepository.FindAllSortedByType((int)Mediatypes.MediaTypes.video_animation);
            List<BEELDBANK_MEDIAOBJECT> sortedList1 = new List<BEELDBANK_MEDIAOBJECT>(list);
            
            XmlDocument xmlDoc = new System.Xml.XmlDocument();
            xmlDoc.Load(ConfigurationManager.AppSettings["Template"] + "\\baseline.xml");

            foreach (BEELDBANK_MEDIAOBJECT mediaObject in sortedList1)
            {
                XmlElement newelem = xmlDoc.CreateElement("mediaobject");
                newelem.InnerXml = ConvertToXml(mediaObject);
                xmlDoc.DocumentElement.InsertAfter(newelem, xmlDoc.DocumentElement.LastChild);
            }

            xmlDoc.Save(ConfigurationManager.AppSettings["appData"] + "\\videoexport.xml");

            DirectoryInfo di = new DirectoryInfo(ConfigurationManager.AppSettings["Export"]);

            // Compress the directory's files.
            foreach (FileInfo fi in di.GetFiles("videoexport.xml"))
            {
                Compress(fi);
            }
        }

        public string ConvertToXml(BEELDBANK_MEDIAOBJECT mediaObject)
        {
            string returnString = "";
            returnString += "<titel><![CDATA[" + mediaObject.Titel + "]]></titel>";
            returnString += "<omschrijving><![CDATA[" + mediaObject.Omschrijving + "]]></omschrijving>";
            returnString += "<datum_realisatie>" + mediaObject.Datum_Realisatie + "</datum_realisatie>";
            returnString += "<filenummer>" + mediaObject.FileNummer + "</filenummer>";
            returnString += "<url>https://beeldbank.rws.nl/MediaObject/Details/" + mediaObject.FileNummer + "</url>";
            returnString += "<typeid>" + mediaObject.TypeId + "</typeid>";
            returnString += "<archiefnummer>" + mediaObject.Archiefnummer + "</archiefnummer>";
            returnString += "<subnummer>" + mediaObject.Subnummer + "</subnummer>";
            returnString += "<mapnummer>" + mediaObject.Mapnummer + "</mapnummer>";
            returnString += "<montage>" + mediaObject.Montage + "</montage>";
            returnString += "<collectie>" + mediaObject.Collectie + "</collectie>";
            returnString += "<lokatie>" + mediaObject.Lokatie + "</lokatie>";
            returnString += "<vervaardiger>" + mediaObject.Vervaardiger + "</vervaardiger>";
            returnString += "<AltTekst>" + mediaObject.AltTekst + "</AltTekst>";
            returnString += "<VideoLengte>" + mediaObject.VideoLengte + "</VideoLengte>";
            returnString += "<hoogte>" + mediaObject.Hoogte + "</hoogte>";
            returnString += "<latitude>" + mediaObject.Latitude + "</latitude>";
            returnString += "<longitude>" + mediaObject.Longitude + "</longitude>";
            returnString += "<trefwoorden>" + mediaObject.Trefwoorden + "</trefwoorden>";
            return returnString;
        }

        public static void Compress(FileInfo fi)
        {
            // Get the stream of the source file.
            using (FileStream inFile = fi.OpenRead())
            {
                // Prevent compressing hidden and 
                // already compressed files.
                if ((File.GetAttributes(fi.FullName)
                    & FileAttributes.Hidden)
                    != FileAttributes.Hidden & fi.Extension != ".gz")
                {
                    // Create the compressed file.
                    using (FileStream outFile =
                                File.Create(fi.FullName + ".gz"))
                    {
                        using (GZipStream Compress =
                            new GZipStream(outFile,
                            CompressionMode.Compress))
                        {
                            // Copy the source file into 
                            // the compression stream.
                            inFile.CopyTo(Compress);

                            Console.WriteLine("Compressed {0} from {1} to {2} bytes.",
                                fi.Name, fi.Length.ToString(), outFile.Length.ToString());
                        }
                    }
                }
            }
        }

        public static void Decompress(FileInfo fi)
        {
            // Get the stream of the source file.
            using (FileStream inFile = fi.OpenRead())
            {
                // Get original file extension, for example
                // "doc" from report.doc.gz.
                string curFile = fi.FullName;
                string origName = curFile.Remove(curFile.Length -
                        fi.Extension.Length);

                //Create the decompressed file.
                using (FileStream outFile = File.Create(origName))
                {
                    using (GZipStream Decompress = new GZipStream(inFile,
                            CompressionMode.Decompress))
                    {
                        // Copy the decompression stream 
                        // into the output file.
                        Decompress.CopyTo(outFile);

                        Console.WriteLine("Decompressed: {0}", fi.Name);
                    }
                }
            }
        }

        public int GenerateInfoTiles()
        {
            try
            {
                ReportRepository reportRepository = new ReportRepository();
                MediaObjectRepository mediaobjectRepository = new MediaObjectRepository();
                StarRatingRepository starRatingRepository = new StarRatingRepository();

                // Generate totale_aantallen_items XML info
                DateTime fromDate = new DateTime(1900, 1, 1);
                DateTime toDate = DateTime.Now;

                IQueryable<CollectionNumbersModel> model = reportRepository.getCollectionNumbersList(fromDate, toDate);

                string returnString = "<tile><visual><binding template=\"TileWideText02\">";
                int i = 1;
                returnString += "<text id=\"" + i++.ToString() + "\">Collectie</text>";  
                foreach (var item in model)
                {
                    returnString += "<text id=\"" + i++.ToString() + "\">" + item.MediaType + "</text>";
                    returnString += "<text id=\"" + i++.ToString() + "\">" + item.ItemCount.ToString() + "</text>";
                }
                returnString += "</binding><binding template=\"TileSquareText01\">";
                i = 1;
                returnString += "<text id=\"" + i++.ToString() + "\">Collectie</text>";
                foreach (var item in model)
                {
                    returnString += "<text id=\"" + i++.ToString() + "\">" + item.MediaType + ": " + item.ItemCount.ToString() + "</text>";
                }
                returnString += "</binding></visual></tile>";

                // Write totale_aantallen_items XML to disk
                using (StreamWriter outfile = new StreamWriter(ConfigurationManager.AppSettings["appData"] + "\\totale_aantallen_items.xml"))
                    outfile.Write(returnString);

                // create MediaServiceController
                MediaServiceController mediaServiceController = new MediaServiceController();
                string size;
                string pictureName;

                // Generate recent_toegevoegde_items XML info
                var mediaObjects = mediaobjectRepository.FindMostRecentCreated(5);

                var saveSquareImageString = "";
                returnString = "<tile><visual><binding template=\"TileWideImageCollection\">";
                i = 1;
                foreach (var mediaObject in mediaObjects)
                {
                    returnString += "<image id=\"" + i.ToString() + "\"";

                    // Generate the thumbnail we need
                    if (i == 1)
                    {
                        size = "_[150x150]";
                        pictureName = "[150x150-";
                    }
                    else
                    {
                        size = "_[75x75]";
                        pictureName = "[75x75-";
                    }
                    pictureName += mediaObject.FileNummer.ToString() + "]";

                    mediaServiceController.CreateImage(pictureName);

                    var tmpString = " src=\"https://beeldbank.rws.nl/Photos/"
                        + (mediaObject.FileNummer / 150).ToString()
                        + "/" + mediaObject.FileNummer.ToString() + size + ".jpg\""
                        + " alt=\"" + mediaObject.AltTekst + "\"/>";

                    // For the Square image only 1 image can be displayed
                    // Store this string for later use
                    if (i == 1)
                        saveSquareImageString = tmpString;

                    returnString += tmpString;
                    i++;
                }
                returnString += "</binding><binding template=\"TileSquareImage\">";
                returnString += "<image id=\"1\"" + saveSquareImageString;
                returnString += "</binding></visual></tile>";

                // Write recent_toegevoegde_items XML to disk
                using (StreamWriter outfile = new StreamWriter(ConfigurationManager.AppSettings["appData"] + "\\recent_toegevoegde_items.xml"))
                    outfile.Write(returnString);


                // Generate recent_aangepaste_items XML info
                mediaObjects = mediaobjectRepository.FindMostRecentModified(5);

                returnString = "<tile><visual><binding template=\"TileWideImageCollection\">";
                i = 1;
                foreach (var mediaObject in mediaObjects)
                {
                    returnString += "<image id=\"" + i.ToString() + "\"";

                    if (i == 1)
                    {
                        size = "_[150x150]";
                        pictureName = "[150x150-";
                    }
                    else
                    {
                        size = "_[75x75]";
                        pictureName = "[75x75-";
                    }
                    pictureName += mediaObject.FileNummer.ToString() + "]";

                    mediaServiceController.CreateImage(pictureName);

                    var tmpString = " src=\"https://beeldbank.rws.nl/Photos/"
                        + (mediaObject.FileNummer / 150).ToString()
                        + "/" + mediaObject.FileNummer.ToString() + size + ".jpg\""
                        + " alt=\"" + mediaObject.AltTekst + "\"/>";

                    // For the Square image only 1 image can be displayed
                    // Store this string for later use
                    if (i == 1)
                        saveSquareImageString = tmpString;

                    returnString += tmpString;
                    i++;
                }
                returnString += "</binding><binding template=\"TileSquareImage\">";
                returnString += "<image id=\"1\"" + saveSquareImageString;
                returnString += "</binding></visual></tile>";

                // Write recent_aangepaste_items XML to disk
                using (StreamWriter outfile = new StreamWriter(ConfigurationManager.AppSettings["appData"] + "\\recent_aangepaste_items.xml"))
                    outfile.Write(returnString);


                // Generate aantal_nieuwe_items XML info
                returnString = "<badge value=\"" + mediaobjectRepository.FindAddedToday().Count().ToString() + "\"/>";

                // Write aantal_nieuwe_items XML to disk
                using (StreamWriter outfile = new StreamWriter(ConfigurationManager.AppSettings["appData"] + "\\aantal_nieuwe_items.xml"))
                    outfile.Write(returnString);


                // Generate foto_met_hoogste_score XML info
                returnString = "<tile><visual><binding template=\"TileWideImage\">";
                
                BEELDBANK_MEDIAOBJECT mediaobject = mediaobjectRepository.FindOne(starRatingRepository.GetTopRatedMediaObject().MediaObjectId);
                                
                mediaServiceController.CreateImage("[300x300-" + mediaobject.FileNummer.ToString() + "]");

                saveSquareImageString = "<image id=\"1\" src=\"https://beeldbank.rws.nl/Photos/" 
                    + (mediaobject.FileNummer / 150).ToString() 
                    + "/" + mediaobject.FileNummer.ToString() + "_[300x300].jpg\""
                    + " alt=\"\"/>";
                returnString += saveSquareImageString;
                returnString += "</binding><binding template=\"TileSquareImage\">";
                returnString += saveSquareImageString;
                returnString += "</binding></visual></tile>";

                // Write aantal_nieuwe_items XML to disk
                using (StreamWriter outfile = new StreamWriter(ConfigurationManager.AppSettings["appData"] + "\\foto_met_hoogste_score.xml"))
                    outfile.Write(returnString);
            }
            catch
            {
                // Return error
                return 1;
            }

            // Return succes;
            return 0;
        }

    }
}